"""
Case Type   : 服务端工具
Case Name   : 脏页数为0时主备切换成功
Create At   : 2024/10/23
Owner       : @xiaqi14
Description :
    1.设置参数
    2.执行sql语句
    3.脏页数为0
    1.设置参数成功
    4.执行主备切换成功
    5.重启数据库
    6.清理环境
Expect      :
    1.设置参数成功
    2.执行sql语句成功
    3.查询成功
    4.执行主备切换成功
    5.重启成功
    6.清理环境成功
History     :
"""
import os
import unittest
import time
from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

COMMONSH = CommonSH("PrimaryDbUser")


@unittest.skipIf(3 != COMMONSH.get_node_num(), '非1+2环境不执行')
class Tools(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.common = Common()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.Constant = Constant()
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.primary_node = Node('PrimaryDbUser')
        self.env_path = macro.DB_ENV_PATH
        self.sta_node = Node('Stabdby1DBuser')
        self.dn_path = macro.DB_INSTANCE_PATH
        self.standby_sh = CommonSH('Stabdby1DBuser')

    def test_gs_om(self):
        text = '----step1:设置参数log_pagewriter=on，bgwriter_delay=10s expect:成功----'
        self.log.info(text)
        excute_cmd = f'''
                    source {self.env_path}
                    gs_guc reload -N all -I all -c "log_pagewriter = on"
                    gs_guc reload -N all -I all -c "bgwriter_delay = 10s"
                    '''
        self.log.info(excute_cmd)
        msg = self.pri_node.sh(excute_cmd).result()
        self.log.info(msg)
        self.asserEqual(2, msg.count(self.Constant.GSGUC_SUCCES_MSG), '执行失败' + text)

        text = '----step2:执行sql语句 expect:成功----'
        self.log.info(text)
        sql_cmd = f'''
                    declare
                        tb_name text;
                        begin
                            for i in 0..100 loop
                                tb_name := 't_gs_om_0128_' || i;
                                execute 'drop table if exists ' || tb_name || ';';
                                execute 'create table ' || tb_name ||'
                                (id1 varchar unique,id2 int primary key,id3 int);';
                                execute 'insert into '
                                || tb_name 
                                || ' values(generate_series(1,5000),
                                generate_series(1,5000),generate_series(1,5000));';
                            end loop;
                        end; 
                    '''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn('ANONYMOUS BLOCK EXECUTE', result, '执行失败' + text)

        text = '----step3:脏页数为0执行成功， expect:成功----'
        self.log.info(text)
        sql_cmd = f'''
                   checkpoint;
                   select remain_dirty_page_num 
                   from dbe_perf.global_pagewriter_status;
                    '''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn('CHECKPOINT', result, '执行失败' + text)
        self.assertIn('0', result, '执行失败' + text)


        text = '----step4执行主备切换 expect:成功----'
        self.log.info(text)
        excute_cmd = f'''
                        source {self.env_path}
                        gs_ctl switchover -D -{self.dn_path}
                        gs_om  -t refreshconf
                        '''
        self.log.info(excute_cmd)
        msg = self.sta_node.sh(excute_cmd).result()
        self.log.info(msg)
        self.asserIn(self.Constant.SWITCHOVER_SUCCESS_MSG, msg, '执行失败' + text)
        self.asserIn(self.Constant.REFRESHCONF_SUCCESS_MSG, msg, '执行失败' + text)
        time.sleep(10)
        sql_cmd = '''
                   select local_role, db_state 
                   from pg_stat_get_stream_replications();
                    '''
        result = self.standby_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.asserIn('Primary', result,'执行失败' + text)

        text = '----step5 重启数据库 expect:成功----'
        self.log.info(text)
        excute_cmd = f'''
                        source {self.env_path}
                        gs_om  -t  restart
                     '''
        self.log.info(excute_cmd)
        msg = self.pri_node.sh(excute_cmd).result()
        self.log.info(msg)
        self.asserIn('Successfully started', msg, '执行失败' + text)

    def tearDown(self):
        text = "----step6：恢复环境 expect:成功----"
        self.log.info(text)
        excute_cmd = f'''
                source {self.env_path}
                gs_ctl switchover -D -{self.dn_path}
                gs_om  -t  refreshconf
                    '''
        self.log.info(excute_cmd)
        msg = self.pri_node.sh(excute_cmd).result()
        self.log.info(msg)
        self.asserIn(self.Constant.SWITCHOVER_SUCCESS_MSG, msg, '执行失败' + text)
        self.asserIn(self.Constant.REFRESHCONF_SUCCESS_MSG, msg, '执行失败' + text)
        sql_cmd = f'''
                    declare
                        tb_name text;
                        begin
                            for i in 0..100 loop
                                tb_name := 't_gs_om_0128_' || i;
                                execute 'drop table if exists ' || tb_name || ';';
                                execute 'create table ' || tb_name ||'
                                (id1 varchar unique,id2 int primary key,id3 int);';
                                execute 'insert into '
                                || tb_name 
                                || ' values(generate_series(1,5000),
                                generate_series(1,5000),generate_series(1,5000));';
                            end loop;
                        end; 
                    '''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn('ANONYMOUS BLOCK EXECUTE', result, '执行失败' + text)
        excute_cmd = f'''
                    source {self.env_path}
                    gs_guc reload -N all -I all -c "log_pagewriter = off"
                    gs_guc reload -N all -I all -c "bgwriter_delay = 2s"
                    '''
        self.log.info(excute_cmd)
        msg = self.pri_node.sh(excute_cmd).result()
        self.log.info(msg)
        self.asserEqual(2, msg.count(self.Constant.GSGUC_SUCCES_MSG), '执行失败' + text)
        self.log.info(f'----{os.path.basename(__file__)} end----')
